package com.guicelcc.guicedemo.poi;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ycp
 * @version V1.0
 * @Description
 * @date 2018/10/21 10:20 PM
 */
public class XlsReader {
    
    public static List<TestData> readFromXlsFile(String path, String keywords)  {
        try {
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
    
            HSSFSheet sheet = workbook.getSheetAt(0);
            List<TestData> result = new ArrayList<>();
            for(int i = 1; i < sheet.getLastRowNum(); i++) {
                HSSFRow row = sheet.getRow(i);
                TestData testData = new TestData();
                testData.setId((int)(row.getCell(4).getNumericCellValue()));
                testData.setType(row.getCell(5).getStringCellValue());
                testData.setQuestion(row.getCell(6).getCellType() == 0 ? String.valueOf(row.getCell(6).getNumericCellValue()) : row.getCell(6).getStringCellValue());
                testData.setAns1(row.getCell(7).getCellType() == 0 ? String.valueOf(row.getCell(7).getNumericCellValue()) : row.getCell(7).getStringCellValue());
                testData.setAns2(row.getCell(8).getCellType() == 0 ? String.valueOf(row.getCell(8).getNumericCellValue()) : row.getCell(8).getStringCellValue());
                testData.setAns3(row.getCell(9).getCellType() == 0 ? String.valueOf(row.getCell(9).getNumericCellValue()) : row.getCell(9).getStringCellValue());
                testData.setAns4(row.getCell(10).getCellType() == 0 ? String.valueOf(row.getCell(10).getNumericCellValue()) : row.getCell(10).getStringCellValue());
                String ans11 = row.getCell(11).getCellType() == 0 ? String.valueOf(row.getCell(11).getNumericCellValue()) : row.getCell(11).getStringCellValue();
                String ans16 = row.getCell(16).getCellType() == 0 ? String.valueOf(row.getCell(16).getNumericCellValue()) : row.getCell(16).getStringCellValue();
                String rightAns = (ans11 == null || "".equals(ans11) )? ans16 : ans11;
                testData.setRightAns(rightAns);
                if(testData.getType().equals(keywords)) {
                    result.add(testData);
                }
            }
            return result;
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    
    public static List<Test2Data> readFromXls2File(String path, String keywords)  {
    	 try {
    		// 创建对Excel工作簿文件的引用
             HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
             HSSFSheet sheet = workbook.getSheetAt(0);
             List<Test2Data> result = new ArrayList<>();
            // HSSFCellStyle cellStyle = workbook.createCellStyle();
            // HSSFDataFormat format= workbook.createDataFormat();
             for(int i = 1; i <= sheet.getLastRowNum(); i++) {
                 HSSFRow row = sheet.getRow(i);
                 Test2Data test2Data = new Test2Data();
                 row.getCell(0).setCellType(XSSFCell.CELL_TYPE_STRING);
                 row.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING);
                 row.getCell(2).setCellType(XSSFCell.CELL_TYPE_STRING);
                 test2Data.setS1(row.getCell(0).getCellType() == 0 ? String.valueOf(row.getCell(0).getNumericCellValue()) : row.getCell(0).getStringCellValue());
                 test2Data.setS2(row.getCell(1).getCellType() == 0 ? String.valueOf(row.getCell(1).getNumericCellValue()) : row.getCell(1).getStringCellValue());
                 test2Data.setS3(row.getCell(2).getCellType() == 0 ? String.valueOf(row.getCell(2).getNumericCellValue()) : row.getCell(2).getStringCellValue());
                 test2Data.setS4(row.getCell(3).getCellType() == 0 ? String.valueOf(row.getCell(3).getNumericCellValue()) : row.getCell(3).getStringCellValue());
                 test2Data.setS5(row.getCell(4).getCellType() == 0 ? String.valueOf(row.getCell(4).getNumericCellValue()) : row.getCell(4).getStringCellValue());
                 test2Data.setS6(row.getCell(5).getCellType() == 0 ? String.valueOf(row.getCell(5).getNumericCellValue()) : row.getCell(5).getStringCellValue());
                 row.getCell(6).setCellType(XSSFCell.CELL_TYPE_STRING);
                 row.getCell(7).setCellType(XSSFCell.CELL_TYPE_STRING);
                // row.getCell(8).setCellType(XSSFCell.CELL_TYPE_STRING);
                 
                // row.getCell(8).getCellStyle().setDataFormat(format.getFormat("#,##0"));
                // cellStyle.setDataFormat(format.getFormat("#,##0")); 
                 test2Data.setS7(row.getCell(6).getCellType() == 0 ? String.valueOf(row.getCell(6).getNumericCellValue()) : row.getCell(6).getStringCellValue());
                 test2Data.setS8(row.getCell(7).getCellType() == 0 ? String.valueOf(row.getCell(7).getNumericCellValue()) : row.getCell(7).getStringCellValue());
                if("".equals(test2Data.getS8()) || null == test2Data.getS8()) {
                	test2Data.setS8("13306162627");
                }
                 test2Data.setS9(row.getCell(8).getCellType() == 0 ? String.valueOf(row.getCell(8).getNumericCellValue()) : row.getCell(8).getStringCellValue());
                 test2Data.setS9(getCommaFormat(new BigDecimal(test2Data.getS9())));
                 result.add(test2Data);
             }
             return result;
    	 } catch (Exception e){
             e.printStackTrace();
         }
    	return null;
    }
    
  //每3位中间添加逗号的格式化显示
  	public static String getCommaFormat(BigDecimal value){
  		return getFormat(",###.##",value);
  	}
  	
  	//自定义数字格式方法
  	public static String getFormat(String style,BigDecimal value){
  		DecimalFormat df = new DecimalFormat();
  		df.applyPattern(style);// 将格式应用于格式化器
  		return df.format(value.doubleValue());
  	}

}
